package gr.unipi.cinema;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

public class Show {
	
	private static Calendar dataTimeToCalendar(Date date, Time time) {
		Calendar cal = Calendar.getInstance();
		cal.setTime(time);
		int hour = cal.get(Calendar.HOUR_OF_DAY);
		int minutes = cal.get(Calendar.MINUTE);
		int seconds = cal.get(Calendar.SECOND);
		
		cal.setTime(date);
		cal.add(Calendar.HOUR, hour);
		cal.add(Calendar.MINUTE, minutes);
		cal.add(Calendar.SECOND, seconds);
		return cal;
	}
	
	public static ShowData[] getShowData(int showId, int movieId, int theaterId, Calendar time) {
		ShowData[] res = null;
		Connection connection = SqlConnection.getConnection();
		if (connection == null) {
			System.out.println("Cloud not connect to db");
			return res;
		}
		
		PreparedStatement st = null;
		ResultSet resultSet = null;
		
		try {
			
			
			String sql =
					"SELECT CS.ID, CS.STARTTIME, CS.MOVIEID, CS.PRICESHOW, " +
					"		CM.MOVIETITLE, CM.IMAGEURL, CH.THEATERID, CH.HALLTITLE, CT.THEATERTITLE, " +
					"		CH.ROWCOUNT, CH.COLCOUNT, COUNT(CRS.ID) SEATSRES " +
					"FROM CSHOW CS " +
					"JOIN CMOVIE CM " +
					"ON CS.MOVIEID = CM.ID " +
					"JOIN CHALL CH " +
					"ON CS.HALLID = CH.ID " +
					"JOIN CTHEATER CT " +
					"ON CH.THEATERID = CT.ID " +
					"LEFT JOIN CRESERVATIONS CR " +
					"ON CR.SHOWID = CS.ID " +
					"LEFT JOIN CRESERVSEATS CRS " +
					"ON CRS.RESERVID = CR.ID " +
					"WHERE CS.ISACTIVE = 1 " +
					"AND CM.MOVIESTATE > 0 " +
					"AND CH.ISACTIVE = 1 " +
					"AND CT.ISACTIVE = 1";
			
			
			if (showId != 0) {
				sql = sql + " AND (CS.ID = " + showId + ") ";
			}
			
			if (movieId != 0) {
				sql = sql + " AND (CM.ID = " + movieId + ") ";
			}
			
			if (theaterId != 0) {
				sql = sql + " AND (CT.ID = " + theaterId + ") ";
			}
			
			if (time == null) {
				sql = sql + " AND (NOW() < CS.STARTTIME) ";
			}
			else {
				sql = sql + " AND (HOUR(TIMEDIFF(?, CS.STARTTIME)) <= 1) " +
						    " AND (HOUR(TIMEDIFF(?, CS.STARTTIME)) >= -1) ";
			}
			
			sql = sql + " GROUP BY CS.ID " +
						" ORDER BY STARTTIME ";
			
			st = connection.prepareStatement(sql);
			
			if (time != null) {
				Date d = time.getTime();
				st.setTimestamp(1, new Timestamp(d.getTime()));
				st.setTimestamp(2, new Timestamp(d.getTime()));
			}
			
			System.out.println("Executing query: " + sql);
			
			resultSet = st.executeQuery();
			
			ArrayList<ShowData> thList = new ArrayList<>(5);
			ShowData entry = null;
			
			while (resultSet.next()) {
				entry = new ShowData(resultSet.getInt("ID"),
										dataTimeToCalendar(resultSet.getDate("STARTTIME"), resultSet.getTime("STARTTIME")),
										resultSet.getInt("MOVIEID"),
										resultSet.getString("MOVIETITLE"),
										resultSet.getString("IMAGEURL"),
										resultSet.getInt("THEATERID"),
										resultSet.getString("THEATERTITLE"),
										resultSet.getDouble("PRICESHOW"),
										resultSet.getString("HALLTITLE"),
										resultSet.getInt("COLCOUNT"),
										resultSet.getInt("ROWCOUNT"),
										resultSet.getInt("SEATSRES")
										);

				thList.add(entry);
			}
			
			res = thList.toArray(new ShowData[0]);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				resultSet = null;
			}
			
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				st = null;
			}
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
		
		return res;
	}
}
